const db = require("../common/db/index")

module.exports = {
    // 发布文章（检查标题是否已存在）
    publish(title, user_id, cate, cate_name, content, src, tag, createAt, status) {
        return new Promise(async (resolve, reject) => {
            const sql = 'SELECT title FROM pre_article WHERE title = ?';
            const rows = await db.query(sql, [title]);
            if (rows.length == 0) {
                const sql2 = 'INSERT INTO pre_article (title, user_id, cate, cate_name, content, src, tag, createAt, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)';
                await db.query(sql2, [title, user_id, cate, cate_name, content, src, tag, createAt, status || 'published'])
                    .then(
                        result => {
                            resolve({
                                msg: "发布文章成功",
                                code: 200,
                                result: result
                            })
                        },
                        err => {
                            reject({
                                msg: "发布文章失败",
                                code: 500,
                                err: err
                            })
                        }
                    )
            }
            else {
                resolve({
                    msg: "文章已经发布",
                    code: 1
                })
            }
        })
    },

    // 获取文章列表（分页，包含作者信息）
    items(page_num, page_size) {
        return new Promise(async (resolve, reject) => {
            const rows = await db.query(`SELECT count(*) as total FROM pre_article`);

            let page_num2 = ((parseInt(page_num) - 1) * parseInt(page_size))
            let page_size2 = (parseInt(page_size))
            let sql = `SELECT a.*, u.username as author FROM pre_article a LEFT JOIN pre_user u ON a.user_id = u.id ORDER BY a.createAt DESC LIMIT ${page_num2} , ${page_size2}`
            await db.query(sql)
                .then(
                    data => {
                        resolve({
                            total: rows[0].total,
                            data,
                            msg: "获取文章列表成功",
                            code: 200
                        })
                    },
                    err => {
                        reject({
                            err,
                            msg: "获取数据失败"
                        })
                    }
                )

        })
    },

    // 删除文章
    remove(id) {
        return new Promise((resolve, reject) => {
            const sql = 'DELETE FROM pre_article WHERE id = ?';
            db.query(sql, [id])
                .then(
                    result => {
                        resolve({
                            code: 200,
                            msg: "删除文章成功",
                            result
                        })
                    },
                    err => {
                        reject({
                            code: -1,
                            err
                        })
                    }
                )

        })
    },

    // 搜索文章（按标题或作者名模糊查询）
    search(keyword) {
        return new Promise((resolve, reject) => {
            const sql = `SELECT a.*, u.username as author 
                         FROM pre_article a 
                         LEFT JOIN pre_user u ON a.user_id = u.id 
                         WHERE a.title LIKE ? OR u.username LIKE ?`;
            db.query(sql, [`%${keyword}%`, `%${keyword}%`])
                .then(
                    result => {
                        resolve({
                            result,
                            msg: "搜索文章成功"
                        })
                    },
                    err => {
                        reject(err)
                    }
                )
        })
    },

    // 查看文章详情（自动增加浏览量）
    detail(id) {
        return new Promise(async (resolve, reject) => {
            try {
                await db.query('UPDATE pre_article SET view_count = view_count + 1 WHERE id = ?', [Number(id)]);
                const sql = `SELECT a.*, u.username as author 
                             FROM pre_article a 
                             LEFT JOIN pre_user u ON a.user_id = u.id 
                             WHERE a.id = ?`;
                const result = await db.query(sql, [Number(id)]);
                resolve(result);
            } catch (err) {
                reject(err);
            }
        })
    },

    // 修改文章（动态检查updateAt字段是否存在）
    modify(title, user_id, cate, cate_name, content, src, tag, id, status, is_top) {
        return new Promise(async (resolve, reject) => {
            try {
                // 检查updateAt字段是否存在
                const checkColumnSql = `
                    SELECT COUNT(*) as count 
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE() 
                    AND TABLE_NAME = 'pre_article' 
                    AND COLUMN_NAME = 'updateAt'
                `;
                const columnCheck = await db.query(checkColumnSql);
                const hasUpdateAt = columnCheck[0].count > 0;

                let sql, params;
                if (hasUpdateAt) {
                    const date = new Date();
                    const updateAt = date.toLocaleDateString() + " " + date.toLocaleTimeString();
                    sql = 'UPDATE pre_article SET title = ?, user_id = ?, cate = ?, cate_name = ?, content = ?, src = ?, tag = ?, status = ?, is_top = ?, updateAt = ? WHERE id = ?';
                    params = [title, user_id, cate, cate_name, content, src, tag, status || 'published', is_top || 0, updateAt, id];
                } else {
                    sql = 'UPDATE pre_article SET title = ?, user_id = ?, cate = ?, cate_name = ?, content = ?, src = ?, tag = ?, status = ?, is_top = ? WHERE id = ?';
                    params = [title, user_id, cate, cate_name, content, src, tag, status || 'published', is_top || 0, id];
                }

                const result = await db.query(sql, params);
                resolve({
                    result,
                    msg: "修改文章成功",
                    code: 200
                });
            } catch (err) {
                reject({
                    msg: "修改文章失败",
                    code: 500,
                    err: err
                });
            }
        })
    },

    // 更新指定分类下的所有文章分类为"暂无分类"
    updateArticlesByCate(parentCate) {
        return new Promise(async (resolve, reject) => {
            try {
                const sql = 'UPDATE pre_article SET cate = ?, cate_name = ? WHERE cate = ?';
                const result = await db.query(sql, ['暂无分类', '暂无分类', parentCate]);
                resolve({
                    code: 200,
                    msg: "更新文章分类成功",
                    result: result
                });
            } catch (err) {
                reject({
                    code: -1,
                    msg: "更新文章分类失败",
                    err: err
                });
            }
        });
    },

    // 获取热门文章（按置顶、浏览量、创建时间排序）
    getHotArticles(limit = 10) {
        return new Promise(async (resolve, reject) => {
            try {
                const sql = `SELECT a.*, u.username as author 
                             FROM pre_article a 
                             LEFT JOIN pre_user u ON a.user_id = u.id 
                             WHERE a.status = 'published' 
                             ORDER BY a.is_top DESC, a.view_count DESC, a.createAt DESC 
                             LIMIT ?`;
                const result = await db.query(sql, [limit]);
                resolve({
                    code: 200,
                    msg: "获取热门文章成功",
                    data: result
                });
            } catch (err) {
                reject({
                    code: -1,
                    msg: "获取热门文章失败",
                    err: err
                });
            }
        });
    },

    // 按分类获取文章列表（分页，可筛选分类）
    list(page_num, page_size, cate_name) {
        return new Promise(async (resolve, reject) => {
            try {
                let whereClause = "WHERE a.status = 'published'";
                let params = [];

                if (cate_name) {
                    whereClause += " AND a.cate_name = ?";
                    params.push(cate_name);
                }

                const countSql = `SELECT count(*) as total FROM pre_article a ${whereClause}`;
                const rows = await db.query(countSql, params);

                let page_num2 = ((parseInt(page_num) - 1) * parseInt(page_size));
                let page_size2 = parseInt(page_size);
                let sql = `SELECT a.*, u.username as author 
                          FROM pre_article a 
                          LEFT JOIN pre_user u ON a.user_id = u.id 
                          ${whereClause}
                          ORDER BY a.is_top DESC, a.createAt DESC 
                          LIMIT ${page_num2}, ${page_size2}`;

                const data = await db.query(sql, params);
                resolve({
                    total: rows[0].total,
                    data,
                    msg: "获取文章列表成功",
                    code: 200
                });
            } catch (err) {
                reject({
                    err,
                    msg: "获取数据失败",
                    code: 500
                });
            }
        });
    },
}